from django.db.models import Avg, Count,Max,Min,Avg,F,Q
from django.db.models.functions import Coalesce
from django.shortcuts import render,HttpResponse
from .models import Book,BookOrder,Publisher,Author

# Create your views here.
## avg 求平均值
def avgview(request):
    #result = Book.objects.aggregate(Avg("price"))
    result = Book.objects.aggregate(书本的平均价格=Avg("price"))
    print(result)
    return HttpResponse(f"平均价格是：{result}")

## Count 获取指定对象的个数
def countview(request):
    #result = Book.objects.aggregate(book_number=Count("price"))
    result = Author.objects.aggregate(email_count = Count("email",distinct=True))
    print(result)
    return HttpResponse(f"邮箱的个数是：{result}")

## max 和 min
def maxminview(request):
    result = Author.objects.aggregate(最大年龄=Max("age"),最小年龄=Min("age"),平均年龄=Avg("age"))
    print(result)
    return HttpResponse(f"年龄信息如下：{result}")

## sum 求指定对象的总和 求图书的销售额
def sumview(request):
    result = Book.objects.annotate(total=Coalesce(Sum("bookorder__price"),(0.0))).values("name","total")
    print(result)
    # 遍历 queryset  先 for 解开成 字典,在对字典访问
    for book in result:
        name = book['name']
        total = book['total']
        print(f"书名：{name},总销售额：{total} 元")
    return HttpResponse(f"销售额度如下：{result}")

## F 表达式
def Fview(request):
    Book.objects.update(price=F("price")+10)
    return HttpResponse("所有图书的价格都已经 加 10")

def Qview1(request):
    books = Book.objects.filter(price__gte=107,rating__gte=4.84)
    for book in books:
        print(book.name,book.price,book.rating)
    return HttpResponse("已找到 图书价格大于 107 且 评分 在 4.84 以上的图书信息")

def Qview2(request):
    books = Book.objects.filter(Q(price__gte=107)| Q(rating__gte=4.84))
    for book in books:
        print(book.name,book.price,book.rating)
    return HttpResponse("已找到 图书价格大于 107 且 评分 或 4.8 以上的图书信息")